package javax.generic.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.generic.persistance.AbstractPersistableEntity;
import javax.generic.swing.ClassUtils;
import javax.generic.swing.FieldInfo;
import javax.generic.swing.MainWin;
import javax.generic.swing.VisualField;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.springframework.core.io.Resource;

public class ExcelService {

	private static final Logger LOG = Logger.getLogger(ExcelService.class);

	public void saveWorkbook(HSSFWorkbook workbook, File file) throws IOException {
		FileOutputStream fos = new FileOutputStream(file);
		workbook.write(fos);
	}

	public HSSFWorkbook fillWorkbook(List<? extends AbstractPersistableEntity> list, String filename, int startRow) throws Exception {
		if (list.isEmpty()) {
			return null;
		}
		AbstractPersistableEntity entity = list.get(0);

		Resource resource = MainWin.CTX.getResource("classpath:xls/" + filename + ".xls");
		HSSFWorkbook workbook;

		boolean isJar = false;
		try {
			resource.getFile();
		} catch (Exception e) {
			isJar = true;
		}
		if (isJar) {
			workbook = new HSSFWorkbook(new POIFSFileSystem(resource.getInputStream()));
		} else {
			workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(resource.getFile())));
		}

		List<FieldInfo> fields = ClassUtils.extractVisualFields(entity.getClass(), false);
		HSSFSheet sheet = workbook.getSheetAt(0);

		int rowNumber = startRow;
		for (AbstractPersistableEntity abstractPersistableEntity : list) {
			HSSFRow oldRow = sheet.getRow(rowNumber + 1);
			sheet.shiftRows(rowNumber, sheet.getLastRowNum(), 1, true, false);

			HSSFRow row = sheet.createRow(rowNumber);

			Map<Integer, FieldInfo> map = new HashMap<Integer, FieldInfo>();

			for (FieldInfo fieldInfo : fields) {

				if (map.containsKey(fieldInfo.getFieldAnotation().xlsCell())) {
					for (FieldInfo fieldInfo2 : fields) {
						if (fieldInfo2.equals(fieldInfo)) {
							break;
						}

						if (fieldInfo2.getFieldAnotation().xlsCell() >= fieldInfo.getFieldAnotation().xlsCell()) {
							fieldInfo2.setXlsCellAdjustment(fieldInfo2.getXlsCellAdjustment() + 1);
						}
					}
				} else {
					map.put(fieldInfo.getFieldAnotation().xlsCell(), fieldInfo);
				}
			}

			for (FieldInfo fieldInfo : fields) {
				VisualField fieldAnotation = ClassUtils.getFieldAnotation(fieldInfo.getField());
				if (fieldAnotation.xlsCell() >= 0) {
					HSSFCell cell = row.createCell((short)(fieldAnotation.xlsCell() + fieldInfo.getXlsCellAdjustment()));

					if (!fieldAnotation.xlsFormula().equals("")) {
						String xlsFormula = fieldAnotation.xlsFormula().toUpperCase();
						cell.setCellFormula(xlsFormula.replace("{ROW}", String.valueOf(row.getRowNum() + 1)));
					} else {
						Object value = ClassUtils.getValue(fieldInfo, abstractPersistableEntity);
						if (value != null) {
							cell.setCellValue(new HSSFRichTextString(value.toString()));
						}
					}
					HSSFCell oldCell = oldRow.getCell(fieldAnotation.xlsCell());
					if (oldCell != null) {
						cell.setCellStyle(oldCell.getCellStyle());
					}
				}
			}
			rowNumber++;
		}
		return workbook;
	}

	public HSSFWorkbook fillWorkbook(AbstractPersistableEntity entity, String filename) throws Exception {

		List<FieldInfo> fields = ClassUtils.extractVisualFields(entity.getClass(), false);

		Resource resource = MainWin.CTX.getResource("classpath:xls/" + filename + ".xls");
		HSSFWorkbook workbook;
		if (resource.isReadable()) {
			workbook = new HSSFWorkbook(new POIFSFileSystem(new FileInputStream(resource.getFile())));
		} else {
			workbook = new HSSFWorkbook(new POIFSFileSystem(resource.getInputStream()));
		}

		HSSFSheet sheet = workbook.getSheetAt(0);

		for (FieldInfo fieldInfo : fields) {
			VisualField fieldAnotation = ClassUtils.getFieldAnotation(fieldInfo.getField());

			HSSFRow row = sheet.getRow(fieldAnotation.xlsRow());
			HSSFCell cell = row.createCell((short)fieldAnotation.xlsCell());

			if (!fieldAnotation.xlsFormula().equals("")) {
				String xlsFormula = fieldAnotation.xlsFormula().toUpperCase();
				try {
					cell.setCellFormula(xlsFormula);
				} catch (Exception e) {
					LOG.error("error for filed formula " + fieldInfo, e);
				}
			} else {
				Object value = ClassUtils.getValue(fieldInfo, entity);

				if (value != null) {
					cell.setCellValue(new HSSFRichTextString(value.toString()));
				}
			}
		}
		return workbook;
	}
}
